package bd;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
import com.sybase.jdbc2.jdbc.SybDriver;

public class BDConnection {

	private Connection conn;
	
	public BDConnection() throws SQLException, IOException, ClassNotFoundException, InstantiationException, IllegalAccessException{
		//Iniciar a base de dados. Os dados sobre a base de dados (localizacao, nome, etc.)sao lidos de um ficheiro.
		String bd_path = null;
		String  base_de_dados = null;
		String bd_user = null;
		String bd_pass = null;
		try {
			//Ler dados da base de dados no ficheiro OpcoesKing.KingOp
			Scanner opcoes = new Scanner(new FileReader("OpcoesKing.KingOp"));
			bd_path = opcoes.nextLine();
			base_de_dados = opcoes.nextLine();
			bd_user = opcoes.nextLine();
			bd_pass = opcoes.nextLine();
		} catch (FileNotFoundException e1) {
			bd_path = "\\";
			base_de_dados = "KINGDB_1_4_4";
			bd_user = "dba";
			bd_pass = "sql";
			FileWriter fw;
			try {
				fw = new FileWriter(new File("OpcoesKing.KingOp"));
				fw.write(bd_path + "\n");
				fw.write(base_de_dados + "\n");
				fw.write(bd_user + "\n");
				fw.write(bd_pass + "\n");
				fw.flush();
				fw.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}

		//Tenta ligar a base de dados. Caso ainda nao exista Servidor Sybase executa um novo Servidor Sybase e tenta estabelecer a ligacao ao mesmo.
		try {
			//			Class.forName("com.sybase.jdbc2.jdbc.SybDriver");
			conn = DriverManager.getConnection("jdbc:sybase:Tds:localhost:2638?ServiceName=" + base_de_dados + "&user=" + bd_user + "&password=" + bd_pass);
		} catch (SQLException sqle) {
				Runtime.getRuntime().exec("dbeng9 " + "\"" + bd_path + base_de_dados + ".db" + "\"");
				Class c = Class.forName("com.sybase.jdbc2.jdbc.SybDriver");
				SybDriver sybDriver = (SybDriver) c.newInstance();
				DriverManager.registerDriver((Driver) sybDriver);
				conn = DriverManager.getConnection ("jdbc:sybase:Tds:localhost:2638?ServiceName=" + base_de_dados + "&user=" + bd_user + "&password=" + bd_pass);
		}finally{
			conn.setAutoCommit(true);
			conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);	//Isolation-level 3 - Serializable (2PPL)
		}
	}

	/**
	 * Comando de busca na base de dados.
	 * 
	 * @param query Comando sql
	 * @return Os Registos encontrados
	 * @throws SQLException
	 */
	public ResultSet executeQuery(String query) throws SQLException {
		Statement stm = conn.createStatement();
		return stm.executeQuery(query);
	}
	
	/**
	 * Comando de actualização na base de dados.
	 * 
	 * @param update Comando SQL
	 * @return Devolve o número de linhas actualizadas, ou devolve 0 para comandos sql que não retornam nada.
	 * @throws SQLException
	 */
	public int executeUpdate(String update) throws SQLException {
		Statement stm = conn.createStatement();
		return stm.executeUpdate(update);
	}
	
	public boolean execute(String update) throws SQLException {
		Statement stm = conn.createStatement();
		return stm.execute(update);
	}
	
	public void closeConnection() throws SQLException{
      		conn.close();
	}
	
	public void setAutoCommit(boolean auto_commit) throws SQLException{
		conn.setAutoCommit(auto_commit);
	}

	public PreparedStatement prepareStatement(String sql) throws SQLException{
		return conn.prepareStatement(sql);
	}

}
